package com.mison.dao;

import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import com.mison.model.MisonFeedback;
import com.mison.model.MisonFeedbackPerson;
import com.mison.model.MisonStudent;

import com.mison.utils.DBUtils;
import com.mison.utils.Page;

public class ChenluDao {
	
	public List<MisonFeedback> getList(Page<MisonFeedback> page) {
		Connection conn = DBUtils .getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		List<MisonFeedback> list = new ArrayList<MisonFeedback>();
		
		String sql = "SELECT *  FROM (mison_feedback as a  left join mison_student as b on a.student_id = b.student_id)  left join mison_feedback_person as d on a.feedback_person_id=d.feedback_person_id limit ?,?;";
		
		try {
			ps = conn.prepareStatement(sql);
			
			ps.setInt(1, (page.getCurrPage()-1)*page.getPerPage());
			ps.setInt(2, page.getPerPage());
			
			System.out.println(ps);
			
			rs = ps.executeQuery();
			

			while(rs.next()){
				MisonFeedback mf = new MisonFeedback();
				MisonStudent ms =new MisonStudent();
				MisonFeedbackPerson mfp =new MisonFeedbackPerson();
				
				
				
			
				mf.setContent(rs.getString("content"));
				mf.setFeedbackId(rs.getInt("feedback_id"));
				mf.setTime(rs.getDate("time"));
				
				mfp.setFeedbackPersonId(rs.getInt("feedback_person_id"));//mison_feedback_person.feedback_person _id
				mfp.setIsLeave(rs.getInt("is_leave"));
				mfp.setName(rs.getString("name"));
				mfp.setPicPath(rs.getString("pic_path"));
				mfp.setSummary(rs.getString("summary"));
				mfp.setType(rs.getInt("type"));//mison.feedback.type,mison_feedback_person.type
				mf.setMisonFeedbackPerson(mfp);//-----------
				ms.setStudentId(rs.getInt("student_id"));
				ms.setAccount(rs.getString("account"));
				ms.setMajor(rs.getString("major"));
				ms.setPicPath(rs.getString("pic_path"));//mison_student.pic_path
				ms.setPsw(rs.getString("psw"));
				ms.setSchool(rs.getString("school"));
				ms.setStudentName(rs.getString("student_name"));
				mf.setMisonStudent(ms);//--------------
				
				list.add(mf);
			}
			
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				ps.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return list;
	}


	public int getCount() {
		Connection conn = DBUtils .getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		String sql = "select count(*) from mison_feedback";
		
		try {
			ps = conn.prepareStatement(sql);
			
			
			System.out.println(ps);
			
			rs = ps.executeQuery();
			
			rs.next();
			
			return rs.getInt("count(*)");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				ps.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return 0;
	}


	public void deleteList(String id) {
		Connection conn = DBUtils .getConnection();
		PreparedStatement ps = null;
		String sql = "delete from mison_feedback where feedback_id=?";
		int i=Integer.parseInt(id);
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1,i);

			System.out.println(ps);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				ps.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}


	public void editList(MisonFeedback mfb) {
		Connection conn = DBUtils .getConnection();
		PreparedStatement ps = null;
		String date = new SimpleDateFormat("yyyy-MM-dd").format(new Date().getTime());
		String sql = "update  mison_feedback set content =?,time = ? where feedback_id = ?;";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, mfb.getContent());
			ps.setString(2, date);
			ps.setInt(3, mfb.getFeedbackId());
			System.out.println(ps);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				ps.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	

}
